<!DOCTYPE html>
<html>
<head>
    <title>第三章:T-SQL</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./T-SQL[未整理].html" class="selected">第三章:T-SQL</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./事务-索引-视图[未整理].html" class="">事务-索引-视图[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./子查询[未整理].html" class="">子查询[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./存储过程[未整理].html" class="">存储过程[未整理]</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 第三章:T-SQL </h1>
            <div class="doc_post"> <blockquote>

<p class="md_block">
    <span class="md_line">变量使用</span>
</p>


<h3 id="toc_1">1.变量的申明</h3>

<p class="md_block">
    <span class="md_line">申明格式</span>
</p>

</blockquote>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="err">变量名</span> <span class="err">数据类型</span><span class="p">;</span> <span class="c1">--后面的分号是可选的</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">数据类型</span>
</p>

<table>
 <thead><tr><th style="text-align:left">类型</th>
<th style="text-align:left">长度</th>
<th style="text-align:left">说明</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">int </td>
<td style="text-align:left"> 4字节 </td>
<td style="text-align:left"> 整数类型,取值范围......</td>
</tr>
<tr><td style="text-align:left">char(n) </td>
<td style="text-align:left"> n:长度 </td>
<td style="text-align:left"> 每一个字符占1个字节,不够长度用空格填充</td>
</tr>
<tr><td style="text-align:left">varchar(n) </td>
<td style="text-align:left"> ...  </td>
<td style="text-align:left"> 可变长度,最大长度为n</td>
</tr>
<tr><td style="text-align:left">nvarchar(n) </td>
<td style="text-align:left"> n:长度 </td>
<td style="text-align:left">  表示支持unicode编码,特点(支持中文,每个字符2字节长度)</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<h3 id="toc_2">2.变量的赋值</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--1.set语句赋值</span>
<span class="k">set</span> <span class="o">@</span><span class="err">变量名</span> <span class="o">=</span> <span class="err">值</span><span class="p">;</span>
<span class="c1">--2.用selecct赋值</span>
<span class="k">select</span> <span class="o">@</span><span class="err">变量名</span> <span class="o">=</span> <span class="err">值</span><span class="p">;</span>
  <span class="c1">--注:select一般用于从数据表中查询一个值赋给变量.</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_3">3.变量的输出(一般用于调试程序)</h3>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--1.print 打印出值,以文本方式</span>
<span class="n">print</span> <span class="o">@</span><span class="err">变量名</span><span class="p">;</span>
<span class="c1">--2.select 输出变量值,以查询结果的网格方查</span>
<span class="k">select</span> <span class="o">@</span><span class="err">变量名</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<h3 id="toc_4">4.全局变量与局部变量</h3>

<p class="md_block md_has_block_below md_has_block_below_blockquote">
    <span class="md_line">全局变量:全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值  调用时 @@变量名<br /></span>
    <span class="md_line">局部变量:用户申明的变量,注意先申明,再赋值使用. 申明格式: @变量名.</span>
</p>


<blockquote>

<p class="md_block">
    <span class="md_line">常用全局变量</span>
</p>

</blockquote>
<table>
 <thead><tr><th style="text-align:left">变量</th>
<th style="text-align:left">含义</th>
 </tr>
</thead>
 <tbody><tr><td style="text-align:left">@@ERROR </td>
<td style="text-align:left"> 最后一个T-SQL错误的错误号()</td>
</tr>
<tr><td style="text-align:left">@@IDENTITY </td>
<td style="text-align:left"><strong>最后一次插入的标识值(注意)</strong></td>
</tr>
<tr><td style="text-align:left">@@ROWCOUNT </td>
<td style="text-align:left"> 受上一个SQL语句影响的行数</td>
</tr>
<tr><td style="text-align:left">@@VERSION </td>
<td style="text-align:left"> SQL Server的版本信息</td>
</tr>
</tbody> 
</table>
<!--block_code_end-->
<p class="md_block">
    <span class="md_line">全局变量:重点撑握:@@identity,下面它的用法:实例,自动生成流水号并得到这个号码.</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="n">id</span> <span class="nb">int</span><span class="p">;</span> <span class="c1">--申明变量</span>
<span class="k">insert</span> <span class="k">into</span> <span class="n">Test</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="k">values</span><span class="p">(</span><span class="s1">&#39;name1&#39;</span><span class="p">)</span> <span class="k">select</span> <span class="o">@</span><span class="n">id</span> <span class="o">=@@</span><span class="k">IDENTITY</span><span class="p">;</span> <span class="c1">--插入数据同时给变量赋值</span>
<span class="n">print</span> <span class="s1">&#39;刚才插入的数据行主键是:&#39;</span><span class="o">+</span> <span class="k">convert</span><span class="p">(</span><span class="nb">varchar</span><span class="p">(</span><span class="mi">5</span><span class="p">),</span><span class="o">@</span><span class="n">id</span><span class="p">);</span> <span class="c1">--输出变量的值</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_5">条件结构</h2>

<p class="md_block">
    <span class="md_line">if(条件)  结构体</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">if</span><span class="p">(</span><span class="mi">1</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span> <span class="n">print</span> <span class="s1">&#39;如果条件成立则执行它&#39;</span>
<span class="c1">--条件体中有多条语句时用begin...end;</span>
<span class="k">if</span><span class="p">(</span><span class="mi">1</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
    <span class="k">begin</span>
    <span class="n">print</span> <span class="s1">&#39;如果条件成立则执行它&#39;</span>
<span class="k">end</span>
<span class="c1">-- if() else</span>
<span class="k">if</span><span class="p">(</span><span class="mi">1</span><span class="o">=</span><span class="mi">2</span><span class="p">)</span>
    <span class="n">print</span> <span class="s1">&#39;条件成立执行&#39;</span><span class="p">;</span>
<span class="k">else</span>
    <span class="n">print</span> <span class="s1">&#39;条件不成立时执行&#39;</span><span class="p">;</span>
<span class="c1">--  if()...slse if()...else 用实例说明</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">a</span> <span class="nb">int</span> 
<span class="k">set</span> <span class="o">@</span><span class="n">a</span> <span class="o">=</span> <span class="mi">4</span>
<span class="k">if</span><span class="p">(</span><span class="o">@</span><span class="n">a</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
    <span class="n">print</span> <span class="s1">&#39;@a的值是1&#39;</span><span class="p">;</span>
<span class="k">else</span> <span class="k">if</span><span class="p">(</span><span class="o">@</span><span class="n">a</span> <span class="o">=</span> <span class="mi">2</span><span class="p">)</span>
    <span class="n">print</span> <span class="s1">&#39;@a的值是2&#39;</span><span class="p">;</span>
<span class="k">else</span> <span class="k">if</span><span class="p">(</span><span class="o">@</span><span class="n">a</span> <span class="o">=</span> <span class="mi">3</span><span class="p">)</span>
    <span class="n">print</span> <span class="s1">&#39;@a的值是3&#39;</span><span class="p">;</span>
<span class="k">else</span>
    <span class="n">print</span> <span class="s1">&#39;@a的值是不知道&#39;</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<hr>

<p class="md_block">
    <span class="md_line">练习:查询出本班<code>JAVA基础</code>平均成绩,如果平均分在70以上，显示“成绩优秀“，并显示前三名学员的考试信息；如果在70以下，显示“本班成绩较差“，并显示后三名学员的考试信息 。</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="k">avg</span> <span class="nb">float</span> <span class="p">;</span> <span class="c1">--申明平均成绩变量</span>
<span class="k">select</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_score</span> 
<span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
<span class="k">if</span><span class="p">(</span><span class="o">@</span><span class="k">avg</span> <span class="o">&gt;</span> <span class="mi">80</span><span class="p">)</span>
    <span class="k">begin</span>
    <span class="n">print</span> <span class="s1">&#39;本班成绩较好,&#39;</span> 
     <span class="k">select</span> <span class="n">top</span> <span class="mi">3</span> <span class="o">*</span> <span class="k">from</span> <span class="n">st_score</span> 
     <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">)</span>
     <span class="k">order</span> <span class="k">by</span> <span class="n">score</span> <span class="k">desc</span>
    <span class="k">end</span>
<span class="k">else</span>
    <span class="k">begin</span>
        <span class="n">print</span> <span class="s1">&#39;本班成绩较差&#39;</span>
        <span class="k">select</span> <span class="n">top</span> <span class="mi">3</span> <span class="o">*</span> <span class="k">from</span> <span class="n">st_score</span> 
        <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">)</span>
        <span class="k">order</span> <span class="k">by</span> <span class="n">score</span> 
    <span class="k">end</span>
<span class="c1">-- 内容改进,以姓名,成绩,科目三列显示----------------------------</span>
    <span class="c1">--查询结果我需要   姓名 成绩 科目 三列</span>
    
<span class="k">declare</span> <span class="o">@</span><span class="k">avg</span> <span class="nb">float</span> <span class="p">;</span> <span class="c1">--申明平均成绩变量</span>
<span class="k">select</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_score</span> 
<span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
<span class="k">if</span><span class="p">(</span><span class="o">@</span><span class="k">avg</span> <span class="o">&gt;</span> <span class="mi">80</span><span class="p">)</span>
    <span class="k">begin</span>
    <span class="n">print</span> <span class="s1">&#39;本班成绩较好,下面是前三名同学的成绩&#39;</span> 
     <span class="k">select</span> <span class="n">top</span> <span class="mi">3</span> <span class="n">b</span><span class="p">.</span><span class="n">name</span> <span class="s1">&#39;姓名&#39;</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">score</span> <span class="s1">&#39;成绩&#39;</span><span class="p">,</span><span class="k">c</span><span class="p">.</span><span class="n">name</span> <span class="s1">&#39;科目&#39;</span> <span class="k">from</span> <span class="n">st_score</span> <span class="n">a</span> 
     <span class="k">left</span> <span class="k">join</span> <span class="n">st_info</span> <span class="n">b</span> <span class="k">on</span> <span class="n">a</span><span class="p">.</span><span class="n">uid</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> <span class="c1">--关联姓名表</span>
     <span class="k">left</span> <span class="k">join</span> <span class="n">subject</span> <span class="k">c</span> <span class="k">on</span> <span class="n">a</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id</span> <span class="c1">--关联科目表</span>
     <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">)</span>
     <span class="k">and</span> <span class="n">score</span> <span class="o">&gt;</span> <span class="mi">0</span> <span class="c1">--因为零分是未参考同学，所以过滤掉</span>
     <span class="k">order</span> <span class="k">by</span> <span class="n">score</span> <span class="k">desc</span>
    <span class="k">end</span>
<span class="k">else</span>
    <span class="k">begin</span>
        <span class="n">print</span> <span class="s1">&#39;本班成绩较差,下面是后三名同学的成&#39;</span>
        <span class="k">select</span> <span class="n">top</span> <span class="mi">3</span> <span class="n">b</span><span class="p">.</span><span class="n">name</span> <span class="s1">&#39;姓名&#39;</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">score</span> <span class="s1">&#39;成绩&#39;</span><span class="p">,</span><span class="k">c</span><span class="p">.</span><span class="n">name</span> <span class="s1">&#39;科目&#39;</span> <span class="k">from</span> <span class="n">st_score</span> <span class="n">a</span> 
        <span class="k">left</span> <span class="k">join</span> <span class="n">st_info</span> <span class="n">b</span> <span class="k">on</span> <span class="n">a</span><span class="p">.</span><span class="n">uid</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> <span class="c1">--关联姓名表</span>
        <span class="k">left</span> <span class="k">join</span> <span class="n">subject</span> <span class="k">c</span> <span class="k">on</span> <span class="n">a</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="k">c</span><span class="p">.</span><span class="n">id</span> <span class="c1">--关联科目</span>
        <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">)</span>
        <span class="k">and</span> <span class="n">score</span> <span class="o">&gt;</span> <span class="mi">0</span> <span class="c1">--因为零分是未参考同学，所以过滤掉</span>
        <span class="k">order</span> <span class="k">by</span> <span class="n">score</span>
    <span class="k">end</span>   
</pre></div>

<!--block_code_end-->
<h2 id="toc_6">While循环</h2>

<p class="md_block">
    <span class="md_line">语法:<br /></span>
    <span class="md_line">while(条件)<br><br /></span>
    <span class="md_line">begin <br /></span>
    <span class="md_line">    ..... <br /></span>
    <span class="md_line">end</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--循环10次,输出0-9的值</span>
<span class="k">DECLARE</span> <span class="o">@</span><span class="n">a</span> <span class="nb">int</span>
<span class="k">set</span> <span class="o">@</span><span class="n">a</span> <span class="o">=</span> <span class="mi">0</span>
<span class="n">WHILE</span><span class="p">(</span><span class="o">@</span><span class="n">a</span> <span class="o">&lt;</span> <span class="mi">10</span><span class="p">)</span>
<span class="k">begin</span>
    <span class="n">print</span> <span class="o">@</span><span class="n">a</span><span class="p">;</span>
    <span class="k">set</span> <span class="o">@</span><span class="n">a</span> <span class="o">=</span> <span class="o">@</span><span class="n">a</span><span class="o">+</span><span class="mi">1</span><span class="p">;</span>
<span class="k">end</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">综合练习:<br /></span>
    <span class="md_line">问题：本次考试成绩较差，假定要提分，确保综合成绩达到优秀。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--模仿课件上写法,用死循环</span>
<span class="k">declare</span> <span class="o">@</span><span class="k">avg</span> <span class="nb">float</span> <span class="p">;</span> <span class="c1">--申明平均成绩变量</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">jia</span> <span class="nb">int</span>    <span class="c1">--用于加分</span>
<span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="mi">0</span>
<span class="n">while</span><span class="p">(</span><span class="mi">1</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span>
<span class="k">begin</span>
    <span class="k">select</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_score</span> 
    <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
    <span class="k">set</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span> <span class="o">@</span><span class="k">avg</span><span class="o">+@</span><span class="n">jia</span>
    <span class="c1">--print @avg</span>
    <span class="k">if</span><span class="p">(</span><span class="o">@</span><span class="k">avg</span> <span class="o">&gt;</span> <span class="mi">80</span><span class="p">)</span>
        <span class="k">begin</span>
        <span class="k">select</span> <span class="n">uid</span><span class="p">,</span><span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="k">from</span> <span class="n">st_score</span> 
        <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
        <span class="n">break</span>
        <span class="k">end</span>
    <span class="k">else</span>
        <span class="k">begin</span>
        <span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="o">@</span><span class="n">jia</span><span class="o">+</span><span class="mi">2</span>
        <span class="n">print</span> <span class="s1">&#39;平均分不够,@jia的值是:&#39;</span><span class="o">+</span> <span class="k">convert</span><span class="p">(</span><span class="nb">varchar</span><span class="p">(</span><span class="mi">10</span><span class="p">),</span><span class="o">@</span><span class="n">jia</span><span class="p">)</span>
        <span class="k">end</span>
</pre></div>

<!--block_code_end-->
<blockquote>

<p class="md_block">
    <span class="md_line">改进版本</span>
</p>

</blockquote>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="k">avg</span> <span class="nb">float</span> <span class="p">;</span> <span class="c1">--申明平均成绩变量</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">jia</span> <span class="nb">int</span>    <span class="c1">--用于加分</span>
<span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">select</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_score</span> 
<span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>

<span class="n">while</span><span class="p">(</span><span class="o">@</span><span class="k">avg</span> <span class="o">&lt;=</span> <span class="mi">80</span><span class="p">)</span>
<span class="k">begin</span>
    <span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="o">@</span><span class="n">jia</span><span class="o">+</span><span class="mi">2</span>
    <span class="k">set</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span> <span class="o">@</span><span class="k">avg</span><span class="o">+</span><span class="mi">2</span>
    <span class="n">print</span> <span class="s1">&#39;平均分不够,@jia的值是:&#39;</span><span class="o">+</span> <span class="s1">&#39;当前平均分是:&#39;</span><span class="o">+</span><span class="k">convert</span><span class="p">(</span><span class="nb">varchar</span><span class="p">(</span><span class="mi">10</span><span class="p">),</span><span class="o">@</span><span class="k">avg</span><span class="p">)</span>
<span class="k">end</span>

<span class="k">select</span> <span class="n">uid</span><span class="p">,</span><span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="k">from</span> <span class="n">st_score</span> 
<span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_7">CASE WHEN THEN ELSE END 语句</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--基本语法</span>
<span class="c1">--简单Case函数</span>
<span class="k">CASE</span> <span class="n">sex</span>
         <span class="k">WHEN</span> <span class="s1">&#39;1&#39;</span> <span class="k">THEN</span> <span class="s1">&#39;男&#39;</span>
         <span class="k">WHEN</span> <span class="s1">&#39;2&#39;</span> <span class="k">THEN</span> <span class="s1">&#39;女&#39;</span>
<span class="k">ELSE</span> <span class="s1">&#39;其他&#39;</span> <span class="k">END</span>
<span class="c1">--Case搜索函数</span>
<span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">sex</span> <span class="o">=</span> <span class="s1">&#39;1&#39;</span> <span class="k">THEN</span> <span class="s1">&#39;男&#39;</span>
         <span class="k">WHEN</span> <span class="n">sex</span> <span class="o">=</span> <span class="s1">&#39;0&#39;</span> <span class="k">THEN</span> <span class="s1">&#39;女&#39;</span>
<span class="k">ELSE</span> <span class="s1">&#39;其他&#39;</span> <span class="k">END</span>
<span class="c1">--case 用于大小于判断</span>
<span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">90</span> <span class="k">and</span> <span class="n">score</span> <span class="o">&lt;=</span> <span class="mi">100</span> <span class="k">THEN</span> <span class="s1">&#39;S&#39;</span>
<span class="k">WHEN</span> <span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">80</span> <span class="k">and</span> <span class="n">score</span> <span class="o">&lt;</span> <span class="mi">90</span> <span class="k">THEN</span> <span class="s1">&#39;A&#39;</span>
<span class="k">WHEN</span> <span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">70</span> <span class="k">and</span> <span class="n">score</span> <span class="o">&lt;</span> <span class="mi">80</span> <span class="k">THEN</span> <span class="s1">&#39;B&#39;</span>
<span class="k">WHEN</span> <span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">60</span> <span class="k">and</span> <span class="n">score</span> <span class="o">&lt;</span> <span class="mi">70</span> <span class="k">THEN</span> <span class="s1">&#39;C&#39;</span>
<span class="k">WHEN</span> <span class="n">score</span> <span class="o">&gt;=</span> <span class="mi">0</span> <span class="k">and</span> <span class="n">score</span> <span class="o">&lt;</span> <span class="mi">60</span> <span class="k">THEN</span> <span class="s1">&#39;D&#39;</span>
<span class="k">ELSE</span> <span class="s1">&#39;成绩有误&#39;</span> <span class="k">END</span>
</pre></div>

<!--block_code_end-->
<blockquote>

<p class="md_block">
    <span class="md_line">上面经过几次循环后,集体平均分&gt;80了,但有些同学分数超过了100分,我们在输出最后结果时进行修改,让成绩最高100分</span>
</p>

</blockquote>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--修正输出结果大于100分的成绩</span>
<span class="k">declare</span> <span class="o">@</span><span class="k">avg</span> <span class="nb">float</span> <span class="p">;</span> <span class="c1">--申明平均成绩变量</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">jia</span> <span class="nb">int</span>    <span class="c1">--用于加分</span>
<span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">select</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_score</span> 
    <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
    
<span class="n">while</span><span class="p">(</span><span class="o">@</span><span class="k">avg</span> <span class="o">&lt;=</span> <span class="mi">80</span><span class="p">)</span>
<span class="k">begin</span>
<span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="o">@</span><span class="n">jia</span><span class="o">+</span><span class="mi">2</span>
<span class="k">set</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span> <span class="o">@</span><span class="k">avg</span><span class="o">+</span><span class="mi">2</span>
<span class="n">print</span> <span class="s1">&#39;平均分不够,@jia的值是:&#39;</span><span class="o">+</span> <span class="s1">&#39;当前平均分是:&#39;</span><span class="o">+</span><span class="k">convert</span><span class="p">(</span><span class="nb">varchar</span><span class="p">(</span><span class="mi">10</span><span class="p">),</span><span class="o">@</span><span class="k">avg</span><span class="p">)</span>
<span class="k">end</span>

<span class="k">select</span> <span class="n">uid</span><span class="p">,</span>
<span class="k">case</span> <span class="k">when</span> <span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="o">&gt;</span> <span class="mi">100</span> <span class="k">then</span> <span class="mi">100</span> <span class="k">else</span> <span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="k">end</span> 
<span class="k">from</span> <span class="n">st_score</span> 
<span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
<span class="cm">/* 结果</span>
<span class="cm">uid         </span>
<span class="cm">----------- ---------------------------------------</span>
<span class="cm">1           86.0</span>
<span class="cm">2           92.0</span>
<span class="cm">3           84.0</span>
<span class="cm">......</span>
<span class="cm">*/</span>
</pre></div>

<!--block_code_end-->
<p class="md_block">
    <span class="md_line">练习:把UID显示成姓名,把成绩变成等级(S,A,B,C,D)......</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">declare</span> <span class="o">@</span><span class="k">avg</span> <span class="nb">float</span> <span class="p">;</span> <span class="c1">--申明平均成绩变量</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">jia</span> <span class="nb">int</span>    <span class="c1">--用于加分</span>
<span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">select</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span><span class="k">AVG</span><span class="p">(</span><span class="n">score</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_score</span> 
    <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">);</span>
    
<span class="n">while</span><span class="p">(</span><span class="o">@</span><span class="k">avg</span> <span class="o">&lt;=</span> <span class="mi">80</span><span class="p">)</span>
<span class="k">begin</span>
<span class="k">set</span> <span class="o">@</span><span class="n">jia</span> <span class="o">=</span> <span class="o">@</span><span class="n">jia</span><span class="o">+</span><span class="mi">2</span>
<span class="k">set</span> <span class="o">@</span><span class="k">avg</span> <span class="o">=</span> <span class="o">@</span><span class="k">avg</span><span class="o">+</span><span class="mi">2</span>
<span class="n">print</span> <span class="s1">&#39;平均分不够,@jia的值是:&#39;</span><span class="o">+</span> <span class="s1">&#39;当前平均分是:&#39;</span><span class="o">+</span><span class="k">convert</span><span class="p">(</span><span class="nb">varchar</span><span class="p">(</span><span class="mi">10</span><span class="p">),</span><span class="o">@</span><span class="k">avg</span><span class="p">)</span>
<span class="k">end</span>

<span class="k">select</span> <span class="n">b</span><span class="p">.</span><span class="n">name</span> <span class="s1">&#39;姓名&#39;</span><span class="p">,</span>
<span class="k">CASE</span> <span class="k">WHEN</span> <span class="n">a</span><span class="p">.</span><span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="o">&gt;=</span> <span class="mi">90</span>  <span class="k">THEN</span> <span class="s1">&#39;A&#39;</span>
         <span class="k">WHEN</span> <span class="n">a</span><span class="p">.</span><span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="o">&gt;=</span> <span class="mi">80</span>  <span class="k">THEN</span> <span class="s1">&#39;B&#39;</span>
         <span class="k">WHEN</span> <span class="n">a</span><span class="p">.</span><span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="o">&gt;=</span> <span class="mi">70</span>  <span class="k">THEN</span> <span class="s1">&#39;C&#39;</span>
        <span class="k">WHEN</span> <span class="n">a</span><span class="p">.</span><span class="n">score</span><span class="o">+@</span><span class="n">jia</span> <span class="o">&gt;=</span> <span class="mi">60</span>  <span class="k">THEN</span> <span class="s1">&#39;D&#39;</span>
        <span class="k">ELSE</span> <span class="s1">&#39;E&#39;</span> <span class="k">END</span> <span class="s1">&#39;成绩&#39;</span>
<span class="k">from</span> <span class="n">st_score</span> <span class="n">a</span>
<span class="k">left</span> <span class="k">join</span> <span class="n">st_info</span> <span class="n">b</span> <span class="k">ON</span> <span class="n">a</span><span class="p">.</span><span class="n">uid</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> 
<span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;JAVA基础&#39;</span><span class="p">)</span>
<span class="cm">/*</span>
<span class="cm">姓名                   成绩</span>
<span class="cm">-------------------- ----</span>
<span class="cm">刘敏                   A</span>
<span class="cm">刘帅                   A</span>
<span class="cm">朱家琪                  A</span>
<span class="cm">杨雯娟                  A</span>
<span class="cm">*/</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>